package dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.*;

import modelo_relacional.*;
import dao_framework.*;

public class CarroDao {
	private dbConn connDB;
	private daoHelper helper;
	private final String dmlInsert;
	private final String dmlDelete;
	private final String dmlUpdate;

	public CarroDao() {
		connDB = new dbConn();
		helper = new daoHelper();
		dmlInsert = "insert into carro (Matricula, Chassi, Marca, Ano, Cavalagem, Kms, Activo) "
				+ "values (?,?,?,?,?,?,?)";
		dmlDelete = "delete from carro where carro_id = ?";
		dmlUpdate = "update carro set Matricula = ?, Chassi = ?, Marca = ?, Ano = ?, Cavalagem = ?, Kms = ?, Activo = ?,"
				+ "Data_Ult_Alteracao = ? where carro_id = ?";
		
	}

	public void dmlCarro(Carro carro, String dml) {
		Connection conn = null;
		PreparedStatement stm = null;
		int carroID = 0;
		
		try {
			connDB.beginTransaction();
			conn = connDB.getConnectionFromContext();
			
			String conv = String.valueOf(carro.getActivo());
			String timeStamp = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(Calendar.getInstance().getTime());
			
			if (dml.equals("Update")) {
				helper.insertDelUpd(conn, dmlUpdate, carro.getMatricula(),carro.getChassi(),carro.getMarca(),carro.getAno(),carro.getCavalagem(),
					carro.getKms(),conv,timeStamp, Integer.toString(carro.getCarro_id()));
			} 
			else if (dml.equals("Insert")) {
				carroID = helper.insertDelUpdReturnGenKey(conn, dmlInsert, carro.getMatricula(),carro.getChassi(),carro.getMarca(),carro.getAno(),carro.getCavalagem(),
						carro.getKms(),conv);
				carro.setCarro_id(carroID);
				
			}
			else if (dml.equals("Delete")) {
				helper.insertDelUpd(conn, dmlDelete, Integer.toString(carro.getCarro_id()));
			}
			
			connDB.endTransaction();
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			connDB.rollbackTransaction();
			throw new InsertUpdateDeleteDaoException("Nao foi possivel realizar a transac��o "+carro,e);
		}
		finally {
			connDB.close(conn);
		}
		
	}
	
	public List<Carro> returnCarroData () {

		Connection conn = null;
		PreparedStatement stm = null;
		ResultSet resultset = null;
		String dmlSelect = "select * from carro order by carro_id asc";
		List<Carro> carros = new ArrayList<Carro>();
		
		try {
			connDB.beginTransaction();
			conn = connDB.getConnectionFromContext();
			carros = setList(helper.getDados(conn,dmlSelect));
			connDB.endTransaction();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally {
			connDB.closeAll(stm, conn);
			connDB.close(resultset);
		}
		
		return carros;
	}
	
	public List<Carro> returnCarroData (Carro carro) {
		Connection conn = null;
		PreparedStatement stm = null;
		ResultSet resultset = null;
		String dmlSelect = "select * from carro where matricula = ? order by carro_id asc";
		List<Carro> carros = new ArrayList<Carro>();
		
		try {
			connDB.beginTransaction();
			conn = connDB.getConnectionFromContext();
			carros = setList(helper.getDados(conn,dmlSelect,carro.getMatricula()));
			connDB.endTransaction();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally {
			connDB.closeAll(stm, conn);
			connDB.close(resultset);
		}
		
		return carros;
	}
	
	public List<Carro> setList (ResultSet rset) {
		List<Carro> carros = new ArrayList<Carro>();
		String activo = null;
		
		try {
			while (rset.next()) {
				Carro carro = new Carro();
				carro.setCarro_id(rset.getInt("Carro_ID"));
				activo = rset.getString("Activo");
				carro.setActivo(activo.charAt(0));
				carro.setAno(rset.getString("Ano"));
				carro.setCavalagem(rset.getString("Cavalagem"));
				carro.setChassi(rset.getString("Chassi"));
				carro.setData_criacao(rset.getString("Data_Criacao"));
				carro.setKms(rset.getString("Kms"));
				carro.setMarca(rset.getString("Marca"));
				carro.setMatricula(rset.getString("Matricula"));
				carros.add(carro);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally {
			connDB.close(rset);
		}
		
		return carros;
	}
}
